﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;

/// <summary>
/// Summary description for Database
/// </summary>
public class Database
{
    public static Database Current { set; get; }

    static Database()
    {
        
        Current = new Database();
    }

	private Database()
	{

	}

    public DataTable GetAllCountries()
    {
        return ExectueQuery("SELECT * FROM [Countries]");
    }
    public DataTable GetFilteredBusinessOpp(int? CountrId,DateTime? Publications)
    {
        StringBuilder strbld = new StringBuilder();
        if(CountrId.HasValue)
        {
            strbld.AppendFormat("CountryId = {0}", CountrId.Value);
        }
        if(Publications.HasValue)
        {
            if (CountrId.HasValue)
                strbld.Append(" AND ");
            strbld.AppendFormat("Publications > N'{0}'", Publications.Value.Date);
        }
        if(strbld.Length > 0)
        {
            strbld.Insert(0, "Where ");
        }
        return ExectueQuery("SELECT * FROM [BusinessOportunities] " + strbld.ToString());
    }

    private DataTable ExectueQuery(string sqlStatment)
    {
        var connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
        var adapter = new SqlDataAdapter(sqlStatment, connectionString);
        //-------------------------------------------------------------------------------------------------
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        return dt;
    }

    public DataTable GetRelatedLinks()
    {
        return ExectueQuery("SELECT * FROM [RelatedLinks]");
    }

    public DataTable GetTop5Articls()
    {
        return ExectueQuery("SELECT TOP 5 * FROM [Seminars]");
    }

    public DataTable GetSeminarbyId(int id)
    {
        return ExectueQuery(string.Format("SELECT * FROM [Seminars] WHERE [ID] = {0}", id));
        
    }
}